Hello,

Can you guys assist in the following scenario where I have two columns  "Staff"  and  "Age"

Staff     Age

John      23

Bob       25

Mike      27

Caren   35

Bonny   42

I'm trying to build a Column chart in SSRS 2008 that would Count th number of staff in every age range (20's , 30's, 40's)

I'm hoping to get three columns in the chart : one field for the 20's  with 3 people in it;    and two other columns for the 30's and 40's with one staff in each.

I can't figure out the expression that should be used in the categories group of the chart.

thanks in advance

Basically I'm trying to group by a range of values (age range 20-29, range 30-39...)  not by a single value.

thanks

insert into #t values ('John',23) insert into #t values ('Bob',25) insert into #t values ('Mike',27) insert into #t values ('Caren',35) insert into #t values ('Bonny',42) create table #ranges (ran_start int,ran_end int) insert into #ranges values(1,9) insert into #ranges values(10,19) insert into #ranges values(20,29) insert into #ranges values(30,39) insert into #ranges values(40,49) select * from #t join  #ranges on Age>=ran_start and Age<=ran_end

Thanks Uri for the reply, but I'm sorry I don't understand how to apply the above to my scenario:

-The values I mentioned are some of a longer list, so I believe the expression that should be used should include something like  "Fields!STAFF.VALUE" and " Fields!age.value"

- As I'll be using these two Fields (staff and age) to build the column chart,  I need the right expression on the Categories Groups function.

The expected chart would show one column for each age range with the number of people in that range.

 

Below is screenshot of the expression box that I'm trying to figure out on BI studio 2008

• Edited by Miled Tuesday, October 04, 2011 2:21 PM

Hi Miled,

Please refer to the following steps to achieve this requirement:
1. In the Category Group properties window, set the “Group on” expression like this:
=Switch(Fields!Age.Value>20 and Fields!Age.Value<29,1,Fields!Age.Value>30 and Fields!Age.Value<39,2,Fields!Age.Value>40 and Fields!Age.Value<49,3)
2. Using the following expression to set the “Label” property:
=Switch(Fields!Age.Value>20 and Fields!Age.Value<29,"20-29",Fields!Age.Value>30 and Fields!Age.Value<39,"30-39",Fields!Age.Value>40 and Fields!Age.Value<49,"40-49")

The result show in the following image:

  
Thanks,
Bin Long

Thanks Elvis for a gr8 solution, i followed your solution but with that i got another problem,

please help me to solve this,

Now my problem is, if Data set bind with chart do not have any row for a particulate category (like "8-14 Days") then chart is not showing data for in with 0 value,

my requirement is if no data available for a age category Zero value is to be displayed like,